#!/use/bin/python
# coding=utf-8
#  主动服务-安装设备量 analysis_app_number
import redis
import datetime
import pymysql

from dbutils.pooled_db import PooledDB


# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.150.109', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# redis
def getReidsConnection():
    redis_info = {
        "host": "172.20.151.90",
        "password": "Coocaa2022",
        "port": 6379,
        "db": 8
    }

    r = redis.Redis(**redis_info, decode_responses=True)
    return r

#根据传入的SQL 返回执行SQL返回的数量
def selectNumBySql(sql,conf=2):
    if conf == 1:
        conn, cur = getConfConnection()
    else:
        conn, cur = getDataConnection()
    #print(sql)
    cur.execute(sql)
    numResult = cur.fetchone()
    num = 0
    if numResult is not None:
        num = numResult[0]
    return num

#计算百分比 保留两位小数  如:34.88%
# X为分子 Y为分母
def getRateByXY(X,Y):
    rate = 0
    if(X is None or Y is None):
        return rate
    if Y != 0:
        if X > Y:
            rate = 100
        else:
            rate = round(X / Y, 4) * 100
    return rate

# 记录 主动服务-相关设备量 analysis_app_number
#date 当天 beforeDate 昨天 serId 服务
def insertAppNumber(date,beforeDate):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_app_number 表是否已经存在当天 数据
        countNum = selectNumBySql(
            f"select count(1) num from analysis_app_number t where 1=1 and t.ref_date =  '{date}'")
        # 获取当日安装APP数量
        installNum = selectNumBySql(
            f"select count(1) from life_assistant_devices t ",1)
        #计算与昨天的差值 作为新增的数量
        beforeInstallNum = selectNumBySql(
            f"select install_num from analysis_app_number t where t.ref_date =  '{beforeDate}'")
        installNewNum = installNum - beforeInstallNum
        # 获取当日支持AI待机的数量数量
        supportAiNum = selectNumBySql(
            f"select count(1) from life_assistant_devices t where  (switches_state1 & (1 << (17 - 1)))<>0", 1)
        # 获取当日支持AI待机及打开AI开关的数量
        aiOpenNum = selectNumBySql(
            f"select count(1) from life_assistant_devices t where ( (switches_state1 & (1 << (17 - 1)))<>0 and 	(switches_state1 & (1 << (18 - 1)))<>0	) ", 1)
        # 计算与昨天的差值 作为新增的数量
        beforeAiOpenNum = selectNumBySql(
            f"select ai_open_num from analysis_app_number t where t.ref_date =  '{beforeDate}'")
        aiOpenNewNum = aiOpenNum - beforeAiOpenNum
        # 生活助手总体订阅量
        subscribeNum = selectNumBySql(
            f"select cumulate_num from analysis_subscribe_number t where t.service_key ='all' and t.ref_date =  '{date}' and t.execute_flag = 0 ")
        if subscribeNum == 0:
            selectSql = f"""select count(1) subNum from 	life_assistant_devices t where ( (switches_state1 & (1 << (1 - 1)))<>0 and 	(switches_state1 & (1 << (2 - 1)))<>0	)
							or((switches_state1 & (1 << (3 - 1)))<>0 and 	(switches_state1 & (1 << (4 - 1)))<>0)
							or((switches_state1 & (1 << (5 - 1)))<>0 and 	(switches_state1 & (1 << (6 - 1)))<>0)
							or((switches_state1 & (1 << (7 - 1)))<>0 and 	(switches_state1 & (1 << (8 - 1)))<>0)
							or((switches_state1 & (1 << (9 - 1)))<>0 and 	(switches_state1 & (1 << (10 - 1)))<>0)"""
            subscribeNum = selectNumBySql(selectSql, 1)
        # 生活助手累计订阅量
        key = f"LOG_DIVICE_ACTIVE_V1_ONCE_ALL"
        # N-7日服务处于打开的用户数
        r = getReidsConnection()
        subscribeCumulateNum = r.scard(key)
        penetrationRate = selectNumBySql(
            f"select penetration_rate from analysis_app_number t where t.ref_date =  '{beforeDate}' ")

        if countNum == 0:
            logDetail = f"""{date}日新增-安装APP数量为{installNum},新增为{installNewNum},支持AI待机的数量为{supportAiNum},支持AI待机及打开AI开关的数量为{aiOpenNum}
                         ,新增为{aiOpenNewNum},生活助手总体订阅量为{subscribeNum},生活助手累计订阅量为{subscribeCumulateNum}"""
            print(logDetail)
            # 插入
            insertSql = f"""insert into analysis_app_number(install_num,install_new_num,support_ai_num,ai_open_num,ai_open_new_num,subscribe_num,subscribe_cumulate_num,penetration_rate,ref_date) 
                value({installNum},{installNewNum},{supportAiNum},{aiOpenNum},{aiOpenNewNum},{subscribeNum},{subscribeCumulateNum},{penetrationRate},'{date}')"""
            cur.execute(insertSql)
        else:
            logDetail = f"""{date}日更新-安装APP数量为{installNum},新增为{installNewNum},支持AI待机的数量为{supportAiNum},支持AI待机及打开AI开关的数量为{aiOpenNum}
                         ,新增为{aiOpenNewNum},生活助手总体订阅量为{subscribeNum},生活助手累计订阅量为{subscribeCumulateNum}"""
            print(logDetail)
            # 更新
            updateSql = f"""
                update analysis_app_number set  install_num = {installNum} ,install_new_num = {installNewNum} , support_ai_num = {supportAiNum} ,ai_open_num = {aiOpenNum} ,ai_open_new_num = {aiOpenNewNum} 
                ,subscribe_num = {subscribeNum},subscribe_cumulate_num = {subscribeCumulateNum},update_time = now()  where  ref_date = '{date}' 
            """
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date

# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getMonthdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m')
    return re_date

def saveLog(detail,ref_date):
    try:
        conn, cur = getDataConnection()
        jobId = 81
        jobName = '核心指标-生活助手-安装设备量'
        tableName = 'analysis_feed_number'
        # 插入日志表
        saveLogSql = f"""insert into log_xxljob_analysis (job_id,job_name,detail,table_name,ref_date) 
                        value({jobId},'{jobName}','{detail}','{tableName}','{ref_date}')"""
        cur.execute(saveLogSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

if __name__ == '__main__':
    d = 1
    date = getdate(d)
    beforeDate = getdate(d+1)
    monthdate = getMonthdate(d)
    #today = "2021-12-13"
    print (f"{date} 日期,主动服务-安装设备量" )
    insertAppNumber(date,beforeDate)
